/**
 * Copyright (c) 2006 RiseSoft Co.,Ltd  
 * $Header$
 */
package cn.newtest.bi.collection.service.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.risesoft.risebi.collection.dao.ReportInstanceDao;
import net.risesoft.risebi.collection.model.EmployeeSoCollection;
import net.risesoft.risebi.collection.model.ReportInstance;
import net.risesoft.risebi.collection.model.StatisticalObjectTree;
import net.risesoft.risebi.collection.model.StatisticalObjectTreeNode;
import net.risesoft.risebi.collection.model.User;
import net.risesoft.risebi.collection.model.UserSO;
import net.risesoft.risebi.collection.service.DbMetaDataManager;
import net.risesoft.risebi.collection.service.DynaBeanManager;
import net.risesoft.risebi.collection.service.EmployeeSoCollectionManager;
import net.risesoft.risebi.collection.service.ReportInstanceManager;
import net.risesoft.risebi.collection.service.SoCollectionManager;
import net.risesoft.risebi.collection.service.UserSOManager;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.appfuse.dao.Dao;
import org.appfuse.service.impl.BaseManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 * 
 * 
 * @author <a href="mailto: baiyibing@gmail.com">王锤</a>
 * @version $Revision$
 * 
 */
@Repository(value = "reportInstanceManager")
@Transactional(rollbackFor = { java.lang.Exception.class })
public class ReportInstanceManagerImpl extends BaseManager implements ReportInstanceManager {	
	private final Log log = LogFactory.getLog(getClass());
	
	private ReportInstanceDao reportInstanceDao;
	private DbMetaDataManager dbMetaDataManager;
	private SoCollectionManager soCollectionManager;
	private EmployeeSoCollectionManager employeeSoCollectionManager;
	private UserSOManager userSOManager;
	private DynaBeanManager dynaBeanManager;
	
	@Autowired
	public void setDynaBeanManager(DynaBeanManager dynaBeanManager) {
		this.dynaBeanManager = dynaBeanManager;
	}

	@SuppressWarnings("unchecked")
	public Map getPagedReportInstancesByObject(ReportInstance reportInstance,int start,int limit) throws Exception {
		String hql = this.getSqlFindObj(reportInstance);
		String countHql = "select count(*) " + hql;
		Integer count = this.getCount(countHql);
		
		List list = this.find(hql, start, limit);
		Map map = new HashMap();
		map.put("count", count);
		map.put("list", list);
		
		return map;
	}
	
	public String getNativeQurySql4SystemUser(
			String databaseName,
			User user,  
			Boolean inWindow,
			String reportVerifyType,
			Integer lockStatus,
			Integer reportStatus,
			String filter,
			String selectedServiceGuid,
			String selectedAppGuid,
			String selectedSoCollectionGuid,
			String selectedStartSotNodeGuid,
			String selectedStartSotNodeInstanceGuid,
			String selectedEndSotNodeGuid) throws Exception
	{
		if(user.getUserType() != User.USERTYPE_SYSTEMMANAGER){
			throw new Exception();
		}
		
		boolean isSuperAdmin = user.isSuperAdmin();
		String baseSql = "FROM Rpt_ReportInstance ri WHERE 1=1 \r\n";
		if(!isSuperAdmin){ //不是超级用户，需指定服务范围			
			if(selectedServiceGuid != null && selectedServiceGuid.trim().length()>0){
				String[] array = selectedServiceGuid.split(",");
				if(array.length==1){
					baseSql = "FROM Rpt_ReportInstance ri WHERE ri.Service_guid='" + selectedServiceGuid + "' \r\n";
				}else{
					String guids = "";
					for(String str : array){
						if(guids.length()==0){
							guids = "'" + str + "'";
						}else{
							guids += ",'" + str + "'";
						}
					}
					baseSql = "FROM Rpt_ReportInstance ri WHERE ri.Service_guid in (" + guids + ") \r\n";
				}
			}else{
				//baseSql = "FROM Rpt_ReportInstance ri WHERE ri.Service_guid is null \r\n";
			}			
		}
		
		if(selectedAppGuid != null && selectedAppGuid.trim().length()>0){
			String[] array = selectedAppGuid.split(",");
			if(array.length==1){
				baseSql += "AND ri.Application_guid='" + selectedAppGuid + "' \r\n";
			}else{
				String guids = "";
				for(String str : array){
					if(guids.length()==0){
						guids = "'" + str + "'";
					}else{
						guids += ",'" + str + "'";
					}
				}
				baseSql += "AND ri.Application_guid in (" + guids + ") \r\n";
			}
		}else{
			//baseSql += "AND ri.Application_guid is null \r\n";
		}

		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String currentDateTime = simpleDateFormat.format(new Date());
		if(inWindow!=null && inWindow){
			if("oracle".equals(databaseName)){
				baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t WHERE t.unlockDate<to_date('" + currentDateTime + "', 'YYYY-MM-DD HH24:MI:SS') AND t.lockDate>to_date('" + currentDateTime + "', 'YYYY-MM-DD HH24:MI:SS')) \r\n";
			}else if("mssql".equals(databaseName)){
				baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t WHERE t.unlockDate<convert(datetime, '" + currentDateTime + "', 120) AND t.lockDate>convert(datetime, '" + currentDateTime + "', 120)) \r\n";
			}if("mysql".equals(databaseName)){
				baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t WHERE t.unlockDate<STR_TO_DATE('" + currentDateTime + "', '%Y-%m-%d %H:%i:%s') AND t.lockDate>STR_TO_DATE('" + currentDateTime + "', '%Y-%m-%d %H:%i:%s')) \r\n";
			}					
		}
		
		if("oracle".equals(databaseName)){
			baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t1 WHERE t1.unlockDate<to_date('" + currentDateTime + "', 'YYYY-MM-DD HH24:MI:SS')) \r\n";
		}else if("mssql".equals(databaseName)){
			baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t1 WHERE t1.unlockDate<convert(datetime, '" + currentDateTime + "', 120)) \r\n";
		}if("mysql".equals(databaseName)){
			baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t1 WHERE t1.unlockDate<STR_TO_DATE('" + currentDateTime + "', '%Y-%m-%d %H:%i:%s')) \r\n";
		}
		
		if(reportVerifyType!=null){
			baseSql += "AND ri.ReportInstance_Guid IN (SELECT reportInstanceGuid from Rpt_ReportVerifyMessage WHERE hidden != 1 AND type IN (" + reportVerifyType + ")) \r\n";
		}
		
		if(lockStatus!=null){
			baseSql += "AND ri.lockStatus=" + lockStatus + " \r\n";					
		}
		
		if(reportStatus!=null){
			baseSql += "AND ri.status=" + reportStatus + " \r\n";					
		}
		
		if(filter!=null && filter.trim().length()>0){
			baseSql += "AND " + filter + " \r\n";
		}
		
		String subQuery = "";		
		// 调查对象集合范围条件
		if(selectedSoCollectionGuid != null && selectedSoCollectionGuid.trim().length()>0)
		{
			subQuery = "(" + soCollectionManager.getSocMembersSQL(selectedSoCollectionGuid,false) + ")";
		}else if(isSuperAdmin==false){ //不是超级用户，不能超越集合范围查询
			//取所有集合的调查对象实例的并集，这相当于该用户有权管理的实例集合。
			List<EmployeeSoCollection> lst = employeeSoCollectionManager.getByUserAndType(selectedAppGuid, user, "1,2,3");
			for(EmployeeSoCollection t : lst){
				if(subQuery.length()==0){
					subQuery = "(" + soCollectionManager.getSocMembersSQL(t.getSoCollection().getGuid(),false) + ")";
				}else{
					subQuery += "\r\n union \r\n" + "(" + soCollectionManager.getSocMembersSQL(t.getSoCollection().getGuid(),false) + ")";
				}						
			}
		}
		
		// 查询组织树条件
		if(selectedStartSotNodeGuid != null	&& selectedStartSotNodeGuid.trim().length()>0
			&& selectedStartSotNodeInstanceGuid != null && selectedStartSotNodeInstanceGuid.trim().length()>0)
		{
			if(subQuery.length()==0){
				subQuery = "(" + soCollectionManager.getSoTreeSQL(selectedStartSotNodeGuid, selectedStartSotNodeInstanceGuid, selectedEndSotNodeGuid,false) + ")";
			}else{
				//取SoCollection的实例集合与查询树的实例集合的交集。
				if("mssql".equals(databaseName) || "oracle".equals(databaseName)){
					subQuery += "\r\n INTERSECT \r\n" + "(" + soCollectionManager.getSoTreeSQL(selectedStartSotNodeGuid, selectedStartSotNodeInstanceGuid, selectedEndSotNodeGuid,false) + ")";
				}else{
					subQuery = "SELECT t.item_guid FROM (\r\n" +  
								subQuery + "\r\n" + 
								") AS t WHERE t.item_guid IN (\r\n" + 
								soCollectionManager.getSoTreeSQL(selectedStartSotNodeGuid, selectedStartSotNodeInstanceGuid, selectedEndSotNodeGuid,false) + 
								")";
				}
			}
		}
		
		if(subQuery.length()>0){
			baseSql += "AND ri.so_instance_guid IN ( \r\n" + 
						subQuery + "\r\n" + 
						") \r\n";
		}
			
		return baseSql;
	}
	
	public String getNativeQurySql4EnterpriseUser(
			String databaseName,
			User user,  
			Boolean inWindow,
			String reportVerifyType,
			Integer lockStatus,
			Integer reportStatus,
			String filter) throws Exception
	{
		if(user.getUserType() != User.USERTYPE_ENTERPRISEUSER){
			throw new Exception();
		}
		
		String baseSql = "FROM Rpt_ReportInstance ri, rpt_application app WHERE ri.Application_guid=app.app_guid AND app.app_status<3 AND app.app_inner=0 \r\n"; //企业填报用户可以跨服务查询
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String currentDateTime = simpleDateFormat.format(new Date());
		if(inWindow!=null && inWindow){
			if("oracle".equals(databaseName)){
				baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t WHERE t.unlockDate<to_date('" + currentDateTime + "', 'YYYY-MM-DD HH24:MI:SS') AND t.lockDate>to_date('" + currentDateTime + "', 'YYYY-MM-DD HH24:MI:SS')) \r\n";
			}else if("mssql".equals(databaseName)){
				baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t WHERE t.unlockDate<convert(datetime, '" + currentDateTime + "', 120) AND t.lockDate>convert(datetime, '" + currentDateTime + "', 120)) \r\n";
			}if("mysql".equals(databaseName)){
				baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t WHERE t.unlockDate<STR_TO_DATE('" + currentDateTime + "', '%Y-%m-%d %H:%i:%s') AND t.lockDate>STR_TO_DATE('" + currentDateTime + "', '%Y-%m-%d %H:%i:%s')) \r\n";
			}					
		}
		
		if("oracle".equals(databaseName)){
			baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t1 WHERE t1.unlockDate<to_date('" + currentDateTime + "', 'YYYY-MM-DD HH24:MI:SS')) \r\n";
		}else if("mssql".equals(databaseName)){
			baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t1 WHERE t1.unlockDate<convert(datetime, '" + currentDateTime + "', 120)) \r\n";
		}if("mysql".equals(databaseName)){
			baseSql += "AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt_ReportPeriod_Template t1 WHERE t1.unlockDate<STR_TO_DATE('" + currentDateTime + "', '%Y-%m-%d %H:%i:%s')) \r\n";
		}
		
		if(reportVerifyType!=null){
			baseSql += "AND ri.ReportInstance_Guid IN (SELECT reportInstanceGuid from Rpt_ReportVerifyMessage WHERE hidden != 1 AND type IN (" + reportVerifyType + ")) \r\n";
		}
		
		if(lockStatus!=null){
			baseSql += "AND ri.lockStatus=" + lockStatus + " \r\n";					
		}
		
		if(reportStatus!=null){
			baseSql += "AND ri.status=" + reportStatus + " \r\n";					
		}
		
		if(filter!=null && filter.trim().length()>0){
			baseSql += "AND " + filter + " \r\n";
		}
		
		String subQuery = "";		
		// 对于主账户来说,先查询RPT_USERSO表，再以查询出的soInstance作为起点，在对应的缺省管理树中查询出所有下级节点
		if(user.getParentUser() == null){
			log.debug("user.getGuid()==" + user.getGuid());
			List<UserSO> lst = userSOManager.getUserSOByUid(user.getGuid());
			for(UserSO uso : lst){					
				StatisticalObjectTree tree = uso.getSo().getApplication().getDefaultSoTree();
				if(tree==null || tree.getGuid().trim().length()==0){
					if(subQuery.length()==0){
						subQuery = "'" + uso.getSoInstanceGuid() + "'";
					}else{
						subQuery += ",'" + uso.getSoInstanceGuid() + "'";
					}
				}else{
					StatisticalObjectTreeNode startNode = tree.getNodeByCatalog(uso.getSo().getGuid());
					List soInstancesList = soCollectionManager.find(soCollectionManager.getSoTreeSQL(startNode.getGuid(), uso.getSoInstanceGuid(), null,false),true);
					for(Object o : soInstancesList){
						if(subQuery.length()==0){
							subQuery = "'" + o.toString().trim() + "'";
						}else{
							subQuery += ",'" + o.toString().trim() + "'";
						}
					}
				}
			}
		}else{
			// 查询RPT_USERSO表，对于子账户来说，查询结果就是该子用户管理的所有调查对象。
			List<UserSO> lst = userSOManager.getUserSOByUid(user.getGuid());
			for(UserSO uso : lst){
				if(subQuery.length()==0){
					subQuery = "'" + uso.getSoInstanceGuid() + "'";
				}else{
					subQuery += ",'" + uso.getSoInstanceGuid() + "'";
				}
			}
		}
		
		
		if(subQuery.length()>0){
			baseSql += "AND ri.so_instance_guid IN ( \r\n" + 
						subQuery + "\r\n" + 
						") \r\n";
		}
			
		return baseSql;
	}
	
	public List<ReportInstance> getUrgencyOrLateReportInstance(int type,List<String> reportPeriodTypes,String serviceGuid,Date currentDate,int start,int limit) throws Exception {
		StringBuffer sb = new StringBuffer();
		sb.append("from ReportInstance r where r.status=0 and (");
		boolean first = true;
		for(String rpt:reportPeriodTypes){
			if(first){
				sb.append("r.reportPeriodType.guid='").append(rpt).append("' ");
				first = false;
			}else
				sb.append("or r.reportPeriodType.guid='").append(rpt).append("' ");
		}
		sb.append(") and r.service.guid='").append(serviceGuid).append("'");
		
		if(type==1){
			sb.append(" and r.reportWindowPeriod.urgeDate < :currentDate ");
		}else if(type==2){
			sb.append(" and r.reportWindowPeriod.lateDate < :currentDate ");
		}else{
			sb.append(" and (r.reportWindowPeriod.urgeDate < :currentDate or r.reportWindowPeriod.lateDate < :currentDate)");
		}
		String hql = sb.toString();
		List<ReportInstance> list = this.findByNamedParam(hql, new String[]{"currentDate"}, new Object[]{currentDate}, start, limit);
		return list;
	}
	
	public void lockServiceReportInstance(String serviceGuid,Date currentDate) throws Exception {
		String sql = "update Rpt_ReportInstance r set r.lockStatus=1 where r.Service_guid='"+serviceGuid+"' and r.ReportPeriod_Template_guid in (select ReportPeriod_Template_guid from rpt_reportperiod_template t where t.lockDate < :currentDate)";
		this.executeNativeSql(sql,new String[]{"currentDate"},new Object[]{currentDate});
		dynaBeanManager.clearSecondCache();
	}
	
	public ReportInstanceDao getReportInstanceDao() {
		return reportInstanceDao;
	}

	@Autowired
	public void setReportInstanceDao(ReportInstanceDao reportInstanceDao) {
		this.reportInstanceDao = reportInstanceDao;
	}

	@SuppressWarnings("unchecked")
	public Dao getDao() {
		return reportInstanceDao;
	}

	public DbMetaDataManager getDbMetaDataManager() {
		return dbMetaDataManager;
	}

	@Autowired
	public void setDbMetaDataManager(DbMetaDataManager dbMetaDataManager) {
		this.dbMetaDataManager = dbMetaDataManager;
	}

	public SoCollectionManager getSoCollectionManager() {
		return soCollectionManager;
	}

	@Autowired
	public void setSoCollectionManager(SoCollectionManager soCollectionManager) {
		this.soCollectionManager = soCollectionManager;
	}

	public EmployeeSoCollectionManager getEmployeeSoCollectionManager() {
		return employeeSoCollectionManager;
	}

	@Autowired
	public void setEmployeeSoCollectionManager(
			EmployeeSoCollectionManager employeeSoCollectionManager) {
		this.employeeSoCollectionManager = employeeSoCollectionManager;
	}

	public UserSOManager getUserSOManager() {
		return userSOManager;
	}

	@Autowired
	public void setUserSOManager(UserSOManager userSOManager) {
		this.userSOManager = userSOManager;
	}

	@Override
	public ReportInstance getReportInstanceBySoCode(String arg0, String arg1,
			String arg2, String arg3) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	
}
